#!/bin/bash
# ========================================
# 数据库初始化脚本
# ========================================

set -e

# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

# 数据库配置（从application-druid.yml读取）
DB_HOST="localhost"
DB_PORT="3306"
DB_NAME="ry-vue"
DB_USER="root"
DB_PASSWORD="123"

# SQL文件目录
SCRIPT_DIR=$(cd "$(dirname "$0")" && pwd)
SQL_DIR="$SCRIPT_DIR"

# 打印带颜色的消息
print_message() {
    local color=$1
    local message=$2
    echo -e "${color}${message}${NC}"
}

# 检查MySQL客户端
check_mysql_client() {
    if ! command -v mysql &> /dev/null; then
        print_message "$RED" "❌ 错误: MySQL客户端未安装"
        print_message "$YELLOW" "安装命令:"
        print_message "$YELLOW" "  CentOS/RHEL: yum install mysql"
        print_message "$YELLOW" "  Ubuntu/Debian: apt install mysql-client"
        exit 1
    fi
    print_message "$GREEN" "✅ MySQL客户端已安装"
}

# 测试数据库连接
test_connection() {
    print_message "$BLUE" "🔌 测试数据库连接..."
    
    if mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASSWORD" -e "SELECT 1;" > /dev/null 2>&1; then
        print_message "$GREEN" "✅ 数据库连接成功"
    else
        print_message "$RED" "❌ 数据库连接失败"
        print_message "$YELLOW" "请检查:"
        print_message "$YELLOW" "  1. 数据库服务是否启动"
        print_message "$YELLOW" "  2. 主机地址: $DB_HOST"
        print_message "$YELLOW" "  3. 端口: $DB_PORT"
        print_message "$YELLOW" "  4. 用户名: $DB_USER"
        print_message "$YELLOW" "  5. 密码是否正确"
        exit 1
    fi
}

# 检查数据库是否存在
check_database() {
    print_message "$BLUE" "🔍 检查数据库 '$DB_NAME'..."
    
    DB_EXISTS=$(mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASSWORD" \
        -e "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='$DB_NAME';" \
        | grep -c "$DB_NAME" || true)
    
    if [ "$DB_EXISTS" -eq 1 ]; then
        print_message "$YELLOW" "⚠️  数据库 '$DB_NAME' 已存在"
        return 0
    else
        print_message "$YELLOW" "⚠️  数据库 '$DB_NAME' 不存在"
        return 1
    fi
}

# 创建数据库
create_database() {
    print_message "$BLUE" "📦 创建数据库 '$DB_NAME'..."
    
    mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASSWORD" <<EOF
CREATE DATABASE IF NOT EXISTS \`$DB_NAME\` 
DEFAULT CHARACTER SET utf8mb4 
DEFAULT COLLATE utf8mb4_general_ci;
EOF
    
    print_message "$GREEN" "✅ 数据库创建成功"
}

# 执行SQL文件
execute_sql_file() {
    local sql_file=$1
    local file_name=$(basename "$sql_file")
    
    print_message "$BLUE" "📝 执行SQL文件: $file_name"
    
    if [ ! -f "$sql_file" ]; then
        print_message "$RED" "❌ 文件不存在: $sql_file"
        return 1
    fi
    
    mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" < "$sql_file"
    
    if [ $? -eq 0 ]; then
        print_message "$GREEN" "  ✅ $file_name 执行成功"
        return 0
    else
        print_message "$RED" "  ❌ $file_name 执行失败"
        return 1
    fi
}

# 初始化基础表
init_base_tables() {
    print_message "$BLUE" "\n========================================="
    print_message "$BLUE" "📊 初始化基础表结构"
    print_message "$BLUE" "========================================="
    
    # 主数据库结构文件
    if [ -f "$SQL_DIR/ry_20240529.sql" ]; then
        execute_sql_file "$SQL_DIR/ry_20240529.sql"
    else
        print_message "$YELLOW" "⚠️  基础表SQL文件不存在，跳过"
    fi
    
    # Quartz定时任务表
    if [ -f "$SQL_DIR/quartz.sql" ]; then
        execute_sql_file "$SQL_DIR/quartz.sql"
    else
        print_message "$YELLOW" "⚠️  Quartz SQL文件不存在，跳过"
    fi
}

# 初始化业务表
init_business_tables() {
    print_message "$BLUE" "\n========================================="
    print_message "$BLUE" "📊 初始化业务表结构"
    print_message "$BLUE" "========================================="
    
    # 学习模块表
    if [ -f "$SQL_DIR/learning_module.sql" ]; then
        execute_sql_file "$SQL_DIR/learning_module.sql"
    else
        print_message "$YELLOW" "⚠️  学习模块SQL文件不存在"
    fi
    
    # 可以添加其他业务模块的SQL文件
    # execute_sql_file "$SQL_DIR/other_module.sql"
}

# 验证表结构
verify_tables() {
    print_message "$BLUE" "\n========================================="
    print_message "$BLUE" "🔍 验证表结构"
    print_message "$BLUE" "========================================="
    
    # 查询表数量
    TABLE_COUNT=$(mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" \
        -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='$DB_NAME';" \
        | tail -n 1)
    
    print_message "$GREEN" "✅ 数据库中共有 $TABLE_COUNT 张表"
    
    # 显示所有表
    print_message "$BLUE" "\n表列表:"
    mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" \
        -e "SHOW TABLES;" | tail -n +2 | while read table; do
        echo "  • $table"
    done
}

# 显示学习模块表
show_learning_tables() {
    print_message "$BLUE" "\n========================================="
    print_message "$BLUE" "📊 学习模块相关表"
    print_message "$BLUE" "========================================="
    
    mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" \
        -e "SHOW TABLES LIKE 'user_%';" | tail -n +2 | while read table; do
        ROW_COUNT=$(mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" \
            -e "SELECT COUNT(*) FROM \`$table\`;" | tail -n 1)
        print_message "$GREEN" "  ✅ $table (记录数: $ROW_COUNT)"
    done
}

# 备份数据库
backup_database() {
    local backup_dir="/backup/mysql"
    local backup_file="$backup_dir/${DB_NAME}_$(date +%Y%m%d_%H%M%S).sql"
    
    print_message "$BLUE" "💾 备份数据库..."
    
    mkdir -p "$backup_dir"
    
    mysqldump -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASSWORD" \
        --single-transaction --quick --lock-tables=false \
        "$DB_NAME" > "$backup_file"
    
    if [ $? -eq 0 ]; then
        print_message "$GREEN" "✅ 备份成功: $backup_file"
        
        # 压缩备份文件
        gzip "$backup_file"
        print_message "$GREEN" "✅ 已压缩: ${backup_file}.gz"
    else
        print_message "$RED" "❌ 备份失败"
    fi
}

# 显示使用说明
show_usage() {
    print_message "$GREEN" "\n========================================="
    print_message "$GREEN" "📖 使用说明"
    print_message "$GREEN" "========================================="
    echo ""
    print_message "$BLUE" "功能选项:"
    print_message "$YELLOW" "  1. 完整初始化 - 创建数据库并初始化所有表"
    print_message "$YELLOW" "  2. 仅初始化表 - 在已有数据库中初始化表"
    print_message "$YELLOW" "  3. 验证表结构 - 检查表是否正确创建"
    print_message "$YELLOW" "  4. 备份数据库 - 导出数据库备份"
    print_message "$YELLOW" "  5. 退出"
    echo ""
}

# 完整初始化
full_init() {
    print_message "$BLUE" "\n========================================="
    print_message "$BLUE" "🚀 开始完整初始化"
    print_message "$BLUE" "========================================="
    
    check_mysql_client
    test_connection
    
    if check_database; then
        read -p "数据库已存在，是否删除重建? (y/n, 默认: n): " -n 1 -r
        echo
        if [[ $REPLY =~ ^[Yy]$ ]]; then
            print_message "$YELLOW" "⚠️  删除数据库..."
            mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASSWORD" \
                -e "DROP DATABASE IF EXISTS \`$DB_NAME\`;"
            print_message "$GREEN" "✅ 数据库已删除"
        else
            print_message "$YELLOW" "⚠️  保留现有数据库"
            return
        fi
    fi
    
    create_database
    init_base_tables
    init_business_tables
    verify_tables
    show_learning_tables
    
    print_message "$GREEN" "\n========================================="
    print_message "$GREEN" "🎉 数据库初始化完成！"
    print_message "$GREEN" "========================================="
}

# 仅初始化表
init_tables_only() {
    print_message "$BLUE" "\n========================================="
    print_message "$BLUE" "📊 初始化表结构"
    print_message "$BLUE" "========================================="
    
    check_mysql_client
    test_connection
    
    if ! check_database; then
        print_message "$RED" "❌ 数据库不存在，请先创建数据库"
        read -p "是否创建数据库? (y/n): " -n 1 -r
        echo
        if [[ $REPLY =~ ^[Yy]$ ]]; then
            create_database
        else
            return
        fi
    fi
    
    init_base_tables
    init_business_tables
    verify_tables
    show_learning_tables
    
    print_message "$GREEN" "\n✅ 表结构初始化完成！"
}

# 主菜单
main_menu() {
    while true; do
        show_usage
        read -p "请选择操作 (1-5): " choice
        
        case $choice in
            1)
                full_init
                ;;
            2)
                init_tables_only
                ;;
            3)
                check_mysql_client
                test_connection
                verify_tables
                show_learning_tables
                ;;
            4)
                check_mysql_client
                test_connection
                backup_database
                ;;
            5)
                print_message "$GREEN" "👋 再见！"
                exit 0
                ;;
            *)
                print_message "$RED" "❌ 无效选择，请重新输入"
                ;;
        esac
        
        echo ""
        read -p "按任意键继续..." -n 1
        echo ""
    done
}

# 主函数
main() {
    print_message "$GREEN" "========================================="
    print_message "$GREEN" "    若依数据库初始化脚本 v1.0"
    print_message "$GREEN" "========================================="
    echo ""
    print_message "$BLUE" "数据库配置:"
    print_message "$BLUE" "  主机: $DB_HOST:$DB_PORT"
    print_message "$BLUE" "  数据库: $DB_NAME"
    print_message "$BLUE" "  用户: $DB_USER"
    echo ""
    
    # 如果提供了参数，直接执行对应功能
    if [ "$1" = "full" ]; then
        full_init
    elif [ "$1" = "tables" ]; then
        init_tables_only
    elif [ "$1" = "verify" ]; then
        check_mysql_client
        test_connection
        verify_tables
        show_learning_tables
    elif [ "$1" = "backup" ]; then
        check_mysql_client
        test_connection
        backup_database
    else
        # 否则显示菜单
        main_menu
    fi
}

# 运行主函数
main "$@"

